package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTGuardrailFaultDto;
import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.GuardrailDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by pjn on 2017/8/14.
 */
public class GuardrailRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<DTGuardrailFaultDto> getGuardrailFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String roadName, String startRoad, String endRoad) throws Exception {
        String sql = "SELECT g.GuardrailId,g.GuardrailPhoto,g.GuardrailProvince,g.GuardrailCity,g.GuardrailArea," +
                "g.GuardrailRegionId,o.OrgName AS guardrailOrgName,u.UserName AS guardrailCreatorName," +
                "g.GuardrailInstallTime\n" +
                "FROM ((tb_guardrail g  JOIN tb_org o ON o.OrgId=g.GuardrailOrgId)\n" +
                "JOIN tb_user u ON u.UserId=g.GuardrailCreatorId)\n" +
                "WHERE g.GuardrailRoadName LIKE '%" + roadName + "%' AND" +
                " g.GuardrailStartRoad LIKE '%" + startRoad + "%' AND g.GuardrailEndRoad LIKE '%" + endRoad + "%'";
        if (actorId != 1) {
            sql += " and g.GuardrailOrgId=" + orgId + "";
        }
        if (startRoad != "" || endRoad != "") {
            sql += " UNION SELECT g.GuardrailId,g.GuardrailPhoto,g.GuardrailProvince,g.GuardrailCity,g.GuardrailArea," +
                    "g.GuardrailRegionId,o.OrgName AS guardrailOrgName,u.UserName AS guardrailCreatorName," +
                    "g.GuardrailInstallTime\n" +
                    "FROM ((tb_guardrail g  JOIN tb_org o ON o.OrgId=g.GuardrailOrgId)\n" +
                    "JOIN tb_user u ON u.UserId=g.GuardrailCreatorId)\n" +
                    "WHERE g.GuardrailRoadName LIKE '%" + roadName + "%' AND" +
                    " g.GuardrailStartRoad LIKE '%" + endRoad + "%' AND g.GuardrailEndRoad LIKE '%" + startRoad + "%'";
        }
        if (actorId != 1) {
            sql += " and g.GuardrailOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTGuardrailFaultDto.class));
        List<DTGuardrailFaultDto> lineFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return lineFaultDtos;
    }

    public List<String> getRoadURoadName(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT g.GuardrailStartRoad AS guardrailStartRoad\n" +
                "FROM tb_guardrail g\n" +
                "WHERE g.GuardrailRoadName LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and g.GuardrailMainOrgId=" + orgId + " ";
            else
                sql += " and g.GuardrailOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT g.GuardrailEndRoad AS guardrailStartRoad\n" +
                "FROM tb_guardrail g\n" +
                "WHERE g.GuardrailRoadName LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and g.GuardrailMainOrgId=" + orgId + " ";
            else
                sql += " and g.GuardrailOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getSectionUStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT g.GuardrailStartRoad AS guardrailStartRoad\n" +
                "FROM tb_guardrail g\n" +
                "WHERE g.GuardrailEndRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and g.GuardrailMainOrgId=" + orgId + " ";
            else
                sql += " and g.GuardrailOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT g.GuardrailEndRoad AS guardrailStartRoad\n" +
                "FROM tb_guardrail g\n" +
                "WHERE g.GuardrailStartRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and g.GuardrailMainOrgId=" + orgId + " ";
            else
                sql += " and g.GuardrailOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<GuardrailDto> getGuardrail4DT(DTRequestDto dtRequestDto,
                                              String actorIds,
                                              Integer orgId,
                                              Boolean audit,
                                              String startRoad,
                                              String endRoad,
                                              String roadName) throws Exception {
        String sql = "select se.*, ur.UserName as guardrailCreatorName, " +
                "og1.OrgName as guardrailOrgName, og2.OrgName as guardrailMainOrgName " +
                "from tb_guardrail se, tb_user ur, tb_org og1, tb_org og2 " +
                "where ur.UserId = se.GuardrailCreatorId " +
                "and og1.OrgId = se.GuardrailOrgId and og2.OrgId = se.GuardrailMainOrgId and " +
                "se.GuardrailRoadName like '%" + roadName + "%' and se.GuardrailStartRoad like '%" + startRoad + "%' " +
                "and se.GuardrailEndRoad like '%" + endRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql += "and guardrailIsComplete = 0 ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and se.GuardrailMainOrgId=" + orgId + " ";
        else
            sql += "and se.GuardrailOrgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += "union select se.*, ur.UserName as guardrailCreatorName, " +
                    "og1.OrgName as guardrailOrgName, og2.OrgName as guardrailMainOrgName " +
                    "from tb_guardrail se, tb_user ur, tb_org og1, tb_org og2 " +
                    "where ur.UserId = se.GuardrailCreatorId " +
                    "and og1.OrgId = se.GuardrailOrgId and og2.OrgId = se.GuardrailMainOrgId and " +
                    "se.GuardrailRoadName like '%" + roadName + "%' and se.GuardrailStartRoad like '%" + endRoad + "%' " +
                    "and se.GuardrailEndRoad like '%" + startRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%'";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and se.GuardrailMainOrgId=" + orgId + " ";
            else
                sql += "and se.GuardrailOrgId = " + orgId + " ";
        }
        sql += "order by GuardrailInstallTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getGuardrail4DTCount(DTRequestDto dtRequestDto,
                                        String actorIds,
                                        Integer orgId,
                                        Boolean audit,
                                        String startRoad,
                                        String endRoad,
                                        String roadName) throws Exception {
        String sql1 = "select count(*) " +
                "from tb_guardrail se, tb_user ur, tb_org og1, tb_org og2 " +
                "where ur.UserId = se.GuardrailCreatorId " +
                "and og1.OrgId = se.GuardrailOrgId and og2.OrgId = se.GuardrailMainOrgId and " +
                "se.GuardrailRoadName like '%" + roadName + "%' and se.GuardrailStartRoad like '%" + startRoad + "%' " +
                "and se.GuardrailEndRoad like '%" + endRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql1 += "and guardrailIsComplete = 0 ";
        if (actorIds.indexOf('1') >= 0)
            sql1 += "";
        else if (actorIds.indexOf('7') >= 0)
            sql1 += "and se.GuardrailMainOrgId=" + orgId + " ";
        else
            sql1 += "and se.GuardrailOrgId = " + orgId + " ";

        Query query1 = em.createNativeQuery(sql1);
        Integer count1 = Integer.parseInt(query1.getSingleResult().toString());
        Integer count2 = 0;
        if (startRoad != "" || endRoad != "") {
            String sql2 = "select count(*) " +
                    "from tb_guardrail se, tb_user ur, tb_org og1, tb_org og2 " +
                    "where ur.UserId = se.GuardrailCreatorId " +
                    "and og1.OrgId = se.GuardrailOrgId and og2.OrgId = se.GuardrailMainOrgId and " +
                    "se.GuardrailRoadName like '%" + roadName + "%' and se.GuardrailStartRoad like '%" + endRoad + "%' " +
                    "and se.GuardrailEndRoad like '%" + startRoad + "%' and se.ProjectNumber like '%" + dtRequestDto.getSearch() + "%'";
            if (actorIds.indexOf('1') >= 0)
                sql2 += "";
            else if (actorIds.indexOf('7') >= 0)
                sql2 += "and se.GuardrailMainOrgId=" + orgId + " ";
            else
                sql2 += "and se.GuardrailOrgId = " + orgId + " ";
            Query query2 = em.createNativeQuery(sql2);
            count2 = Integer.parseInt(query2.getSingleResult().toString());
        }
        return count2 + count1;
    }

    public List<String> getStartRoadByRoadName(String roadName, String actorIds, Integer orgId) throws Exception {
        String sql = "select  GuardrailStartRoad as roadName from tb_guardrail where GuardrailRoadName like '%" + roadName + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and guardrailMainOrgId=" + orgId + " ";
        else
            sql += "and guardrailOrgId = " + orgId + " ";
        sql += "union select GuardrailEndRoad as roadName from tb_guardrail where GuardrailRoadName like '%" + roadName + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and guardrailMainOrgId=" + orgId + " ";
        else
            sql += "and guardrailOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getEndRoadByStartRoad(String guardrailRoadName, String startRoad, String actorIds, Integer orgId) throws Exception {
        String sql = "select GuardrailStartRoad as roadName from tb_guardrail where GuardrailRoadName like '%" + guardrailRoadName + "%'" +
                " and  GuardrailEndRoad like '%" + startRoad + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and guardrailMainOrgId=" + orgId + " ";
        else
            sql += "and guardrailOrgId = " + orgId + " ";
        sql += "union select GuardrailEndRoad as roadName from tb_guardrail where GuardrailRoadName like '%" + guardrailRoadName + "%'" +
                " and  GuardrailStartRoad like '%" + startRoad + "%'  ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and guardrailMainOrgId=" + orgId + " ";
        else
            sql += "and guardrailOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<GuardrailDto> getGuardrailCoordinates4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception {
        String sql = "SELECT CoordinateTypeId as elementId , " +
                "GROUP_CONCAT(CoordinateLongitude order by CoordinateOrder) as longitude, " +
                "GROUP_CONCAT(CoordinateLatitude order by CoordinateOrder) as latitude " +
                "from tb_coordinate where CoordinateType = 0 ";
        if (actorId != 1) {
            if (orgType == 0)
                sql += "and CoordinateTypeId in(SELECT GuardrailId FROM tb_guardrail where guardrailOrgId=" + orgId + ") ";
            else if (orgType == 1)
                sql += "and CoordinateTypeId in(SELECT GuardrailId FROM tb_guardrail where guardrailMainOrgId=" + orgId + ") ";
        }
        sql += "  group by CoordinateTypeId ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailDto.class));
        return query.getResultList();
    }

    public List<String> getCity4Guardrail(String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT DISTINCT GuardrailCity from tb_guardrail ";
        if (actorIds.indexOf('1') >= 0) ;
        else if (actorIds.indexOf('7') >= 0)
            sql += " where GuardrailMainOrgId=" + orgId + " ";
        else
            sql += " where GuardrailOrgId=" + orgId + "";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getStartRoadByCity(String city, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT sm.GuardrailStartRoad AS guardrailStartRoad " +
                "FROM tb_guardrail sm where sm.guardrailCity like '%" + city + "%'";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.GuardrailMainOrgId=" + orgId + " ";
        else
            sql += " and sm.GuardrailOrgId=" + orgId + " ";
        sql += " UNION SELECT sm.GuardrailEndRoad AS guardrailStartRoad " +
                "FROM tb_guardrail sm where sm.guardrailCity like '%" + city + "%'";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.GuardrailMainOrgId=" + orgId + " ";
        else
            sql += " and sm.GuardrailOrgId=" + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getSectionByStartRoad4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT sm.GuardrailStartRoad AS guardrailStartRoad " +
                "FROM tb_guardrail sm " +
                "WHERE sm.GuardrailEndRoad LIKE '%" + roadName + "%' and sm.GuardrailCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.GuardrailMainOrgId=" + orgId + " ";
        else
            sql += " and sm.GuardrailOrgId=" + orgId + " ";

        sql += " UNION SELECT sm.GuardrailEndRoad AS guardrailStartRoad " +
                "FROM tb_guardrail sm " +
                "WHERE sm.GuardrailStartRoad LIKE '%" + roadName + "%' and sm.GuardrailCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and sm.GuardrailMainOrgId=" + orgId + " ";
        else
            sql += " and sm.GuardrailOrgId=" + orgId + " ";

        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<GuardrailDto> getGuardrail4APP(String actorIds, Integer orgId, Integer mCount, Integer mSize, String city, String startRoad, String endRoad, String roadName) throws Exception {
        String sql = "select m.*, ur.UserName as guardrailCreatorName, " +
                "og1.OrgName as guardrailOrgName, og2.OrgName as guardrailMainOrgName " +
                "from tb_guardrail m, tb_user ur, tb_org og1, tb_org og2 " +
                "where ur.UserId = m.GuardrailCreatorId " +
                "and og1.OrgId = m.GuardrailOrgId and og2.OrgId = m.GuardrailMainOrgId " +
                "and m.GuardrailStartRoad like '%" + startRoad + "%' and m.GuardrailEndRoad like '%" + endRoad + "%' " +
                "and m.GuardrailRoadName like '%" + roadName + "%' and m.GuardrailCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and m.GuardrailMainOrgId=" + orgId + " ";
        else
            sql += "and m.GuardrailOrgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += "union select m.*, ur.UserName as guardrailCreatorName," +
                    "og1.OrgName as guardrailOrgName, og2.OrgName as guardrailMainOrgName " +
                    "from tb_guardrail m, tb_user ur, tb_org og1, tb_org og2 " +
                    "where ur.UserId = m.GuardrailCreatorId " +
                    "and og1.OrgId = m.GuardrailOrgId and og2.OrgId = m.GuardrailMainOrgId " +
                    "and m.GuardrailStartRoad like '%" + endRoad + "%' and m.GuardrailEndRoad like '%" + startRoad + "%' " +
                    "and m.GuardrailRoadName like '%" + roadName + "%' and m.GuardrailCity like '%" + city + "%' ";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and m.GuardrailMainOrgId=" + orgId + " ";
            else
                sql += "and m.GuardrailOrgId = " + orgId + " ";
        }
        sql += "order by concat(GuardrailInstallTime, guardrailId) desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailDto.class));
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<GuardrailDto> getGuardrailById(Integer guardrailId) throws Exception {
        String sql = "select se.* ,o1.orgName as guardrailOrgName,o2.OrgName as guardrailMainOrgName," +
                " u.userName as guardrailCreatorName from tb_guardrail se,tb_org o1,tb_user u,tb_org o2 " +
                "where o1.orgId=se.guardrailOrgId and o2.orgId=se.guardrailMainOrgId and u.UserId=se.guardrailCreatorId" +
                " and se.guardrailId=" + guardrailId + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailDto.class));
        return query.getResultList();
    }

    public List<String> getRoadName4GuardrailByCity(String actorIds, Integer orgId, String city, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT GuardrailRoadName from  tb_guardrail where GuardrailCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and guardrailMainOrgId=" + orgId + " ";
        else
            sql += " and guardrailOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getEndRoadByStartRoad4App(String city, String roadName, String startRoad, String actorIds, Integer orgId, Integer mCount, Integer mSize) {
        String sql = "select GuardrailStartRoad as roadName from tb_guardrail where GuardrailRoadName like '%" + roadName + "%'" +
                " and  GuardrailEndRoad like '%" + startRoad + "%' and GuardrailCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and guardrailMainOrgId=" + orgId + " ";
        else
            sql += "and guardrailOrgId = " + orgId + " ";
        sql += "union select GuardrailEndRoad as roadName from tb_guardrail where GuardrailRoadName like '%" + roadName + "%'" +
                " and  GuardrailStartRoad like '%" + startRoad + "%' and GuardrailCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and gardrailMainOrgId=" + orgId + " ";
        else
            sql += "and guardrailOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getStartRoadByRoadName4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "select  GuardrailStartRoad as roadName from tb_guardrail where GuardrailRoadName like '%" + roadName + "%' " +
                "and GuardrailCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and guardrailMainOrgId=" + orgId + " ";
        else
            sql += "and guardrailOrgId = " + orgId + " ";
        sql += "union select GuardrailEndRoad as roadName from tb_guardrail where GuardrailRoadName like '%" + roadName + "%' " +
                "and GuardrailCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and guardrailMainOrgId=" + orgId + " ";
        else
            sql += "and guardrailOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<GuardrailDto> getGuardrail4Map4App(Integer orgId,
                                                   String actorIds,
                                                   double neLongitude,
                                                   double neLatitude,
                                                   double swLongitude,
                                                   double swLatitude) throws Exception {
        String sql = "select * from tb_guardrail ";
        sql += "where guardrailId in ( " +
                "select CoordinateTypeId from tb_coordinate where CoordinateType = 0 " +
                "and CoordinateLongitude < :neLongitude and CoordinateLongitude > :swLongitude " +
                "and CoordinateLatitude < :neLatitude and CoordinateLatitude > :swLatitude " +
                "group by CoordinateType, CoordinateTypeId)";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and guardrailMainOrgId = " + orgId + " ";
        else
            sql += "and guardrailOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        query.setParameter("neLongitude", String.valueOf(neLongitude));
        query.setParameter("swLongitude", String.valueOf(swLongitude));
        query.setParameter("neLatitude", String.valueOf(neLatitude));
        query.setParameter("swLatitude", String.valueOf(swLatitude));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailDto.class));
        return query.getResultList();
    }

}
